package ikar.nrd.fillingref.generator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DataBaseManager {

	private Connection connection = null;
	private Statement statement = null;

	private final List<String> generatedPlateCodesList = new ArrayList<String>();

	public static final String TEMP_TABLES_NAME = "#FILLREF";

	public static String PLATE_CODE_INDEX = "";

	private final String patternId;

	public DataBaseManager(String patternId) {
		this.patternId = patternId;
	}

	public String getPatternId() {
		return patternId;
	}

	public static String getCreateTempTableScript() {
		return "USE NRD\n GO \n IF OBJECT_ID('tempdb.." + TEMP_TABLES_NAME + "') IS NOT NULL \n" + "drop table " + TEMP_TABLES_NAME + " " + "\nGO\n" + "CREATE TABLE " + TEMP_TABLES_NAME
				+ "(ID INT,PATTERN_ID INT,PARENT_FIELD_ID INT,PARENT_PLATE_CODE NVARCHAR(500),XPATH NVARCHAR(500),PLATE_CODE NVARCHAR(500),NAME NVARCHAR(500))" + "\nGO\n";
	}

	public void dbConnect(String url) throws Exception {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			connection = DriverManager.getConnection(url);
			statement = connection.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	protected String getFieldId(String rootElementId, String elementName) {
		String fieldId = null;
		if (rootElementId == null) {
			rootElementId = "-1";
		}
		String query = "SELECT FIELD_ID FROM nrd.filling_ref WHERE PATTERN_ID=" + patternId + " AND isnull(parent_field_id, -1) =" + rootElementId + " AND (xpath = './" + elementName + "' OR xpath = './" + elementName + "/value' OR xpath = '" + elementName + "' OR xpath = '" + elementName
				+ "/value')";
		try {
			ResultSet rs = statement.executeQuery(query);
			if (rs.next()) {
				fieldId = rs.getString(1);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return fieldId;
	}

	protected String getOrGeneratePlateCode(String currentPlateCode, String rootElementId, String elementName) {
		String fieldId = getFieldId(rootElementId, elementName);
		if (fieldId == null) {
			return generatePlateCode(elementName, currentPlateCode, patternId);
		} else {
			String plateCode = getPlateCode(patternId, fieldId);
			if (plateCode == null) {
				return generatePlateCode(elementName, currentPlateCode, patternId);
			}
			return plateCode;
		}
	}

	/**
	 * 
	 * @param patternId
	 * @param fieldId
	 * @return
	 */
	protected String getPlateCode(String patternId, String fieldId) {
		if (fieldId == null) {
			return null;
		}
		String query = "SELECT PLATE_CODE FROM nrd.filling_ref WHERE PATTERN_ID=" + patternId + " AND field_id =" + fieldId;
		try {
			ResultSet rs = statement.executeQuery(query);
			if (rs.next()) {
				fieldId = rs.getString(1);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return fieldId;
	}

	protected boolean isThePlateCodeExists(String plateCode, String patternId) {
		ResultSet rs = null;
		String query = "SELECT 1 FROM nrd.filling_ref WHERE PATTERN_ID=" + patternId + " AND PLATE_CODE ='" + plateCode + "'";
		try {
			rs = statement.executeQuery(query);
			if (rs.next()) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
				}
		}
	}

	/**
	 * 
	 * @param elementName
	 * @param rootElementName
	 * @param patternId
	 * @return
	 */
	protected String generatePlateCode(String elementName, String currentPlateCode, String patternId) {
		if (currentPlateCode == null) {
			currentPlateCode = "TABLE";
		} else if (!currentPlateCode.contains(PLATE_CODE_INDEX)) {
			currentPlateCode = PLATE_CODE_INDEX + currentPlateCode;
		}

		String newPlateCode = "";
		/*
		 * if (Arrays.asList(currentPlateCode.split("_")).size() >= 4) { String index = ""; for (int i = currentPlateCode.split("_").length - 3; i <
		 * currentPlateCode.split("_").length - 1; i++) { if (!"TABLE".equals(currentPlateCode.split("_")[i])) { String chain = currentPlateCode.split("_")[i]; index +=
		 * chain.subSequence(0, chain.length() > 6 ? 6 : chain.length()) + "_"; // index += currentPlateCode.split("_")[i] + "_"; } } newPlateCode = index +
		 * currentPlateCode.substring(currentPlateCode.lastIndexOf("_") + 1, currentPlateCode.length()); } else { newPlateCode = currentPlateCode; }
		 */

		newPlateCode = currentPlateCode + "_" + elementName.toUpperCase();
		Integer plateCodeIndex = 0;
		while (isThePlateCodeExists(newPlateCode, patternId)) {
			newPlateCode += (plateCodeIndex++).toString();
		}
		return newPlateCode;
	}

	/**
	 * 
	 * @param patternId
	 * @param rootElementId
	 * @param rootElementName
	 * @param elementName
	 * @param parentPlateCode
	 * @param isAttribute
	 * @return
	 */
	protected String nodeInsertQuery(String rootElementId, String rootElementName, String elementName, String parentPlateCode, String plateCode) {

		String fieldId = getFieldId(rootElementId, elementName);
		if (rootElementId == null) {
			rootElementId = "NULL";
		}
		if (fieldId == null) {
			if (parentPlateCode != null) {
				elementName = "./" + elementName;
				parentPlateCode = "'" + parentPlateCode + "'";
			}
			String query = "INSERT INTO " + TEMP_TABLES_NAME + "(PATTERN_ID,PARENT_FIELD_ID,PARENT_PLATE_CODE,XPATH,PLATE_CODE)";
			query += " values(" + patternId + "," + rootElementId + "," + parentPlateCode + ",'" + elementName + "','" + plateCode + "') \n";
			return query;
		} else {
			return "";
		}
	}

}
